<html>
<head>
<link rel="stylesheet" type="text/css" href="../../../../build/resources/javadoc.css" title="style">
</head>
<body>

SsTemplates is a simple yet powerful framework for creating Excel documents in a Java environment.

<h2><a name="documentation">SsTemplates Spreadsheet Templates for Excel</a></h2>

<p>
SsTemplates produces Excel documents using XML templates. The model is very similar to producing HTML using JSP pages. You can use SsTemplates as a standalone component for producing Excel documents in your Java application or as a servlet for rending Excel documents from your web application.
</p>

<p>
SsTemplates is supported by <a target="_top" href="http://www.carbonfive.com/">Carbon Five</a>.
</p>

<ul>
<li><a href="package.html#overview">Overview</a>
<li><a href="package.html#examples">Examples</a>
<li><a href="package.html#features">Features</a>
<li><a href="package.html#usage">Usage</a>
<li><a href="package.html#download">Download</a>
<li><a href="package.html#prerequisites">Prerequisites</a>
<li><a href="package.html#install">Install</a>
<li><a href="package.html#support">Support</a>
<li><a href="package.html#developers">Developers</a>
<li><a href="package.html#reference">Reference</a>
<li><a href="package.html#license">License</a>
<li><a href="package.html#credits">Credits</a>
</ul>

<hr size="1">

<h3><a name="overview">Overview</a></h3>

<p>
Building business applications at Carbon Five, we have found Excel to be an invaluable tool for putting data in the hands of business users. Business users use Excel daily to aggregate and organize information, slice and dice data, and produce charts and reports for printing and digital distribution. HTML-based reporting systems rarely meet the needs of business users as well as Excel-based reports.
</p>

<p>
Through building solutions to meet the needs of business users, we developed SsTemplates to give web developers a simple tool to produce meticulously formatted, data-rich Excel documents. SsTemplates is designed to provide a development model very similar to producing HTML pages using JSP and JSTL (the Java Standard Tag Library).
</p>

<h3><a name="examples">Examples</a></h3>

<p>
Here are a few simple examples to illustrate. These examples are included with the SsTemplates distribution. Exhaustive examples are included in the SsTemplates test suite for all SsTemplates features.
</p>

<h4>Basic</h4>

<p>
SsTemplates provides a basic XML syntax for describing Excel workbooks, sheets, rows and cell.
</p>

<code>basic.sst</code>

<pre class="code">
&lt;workbook>
  &lt;sheet name="Basic Spreadsheet">
    &lt;row>
      &lt;cell>Text in a cell&lt;/cell>
    &lt;/row>
  &lt;/sheet>
&lt;/workbook>
</pre>

<p>
<img src="doc-files/basic.png" alt="Basic Example">
</p>

<h4>Formatting</h4>

<p>
The full formatting options in Excel are available using the <code>&lt;style></code> tag. There are many options for styles that can be set and their possible values. You can use either standard color named or hex codes for color values.
</p>

<p>
TODO: document all style attributes and values. For now, see <code>com.carbonfive.sstemplates.tag.StyleTag</code> source.
</p>

<code>format.sst</code>

<pre class="code">
&lt;workbook bgcolor="#FFFFFF">
  &lt;sheet name="Formatted Spreadsheet">
    &lt;row>
      &lt;style fontColor="green" fontWeight="bold" 
             foreground="#C9C9C9" fillPattern="solid-foreground">
        &lt;cell colspan="3">Formatting Example&lt;/cell>
      &lt;/style>
    &lt;/row>
    &lt;row/>
    &lt;row>
      &lt;cell>Value 1&lt;/cell>
      &lt;style border="medium-dashed" borderColor="blue" dataFormat="$0.00">
        &lt;cell type="numeric">2&lt;/cell>
      &lt;/style>
      &lt;cell>Value 3&lt;/cell>
    &lt;/row>
  &lt;/sheet>
&lt;/workbook>
</pre>

<p>
<img src="doc-files/format.png" alt="Named Styles Example">
</p>

<h4>Named Styles</h4>

<p>
Named styles work like CSS classes. They let you define styles in one place and then use them by name later.
</p>

<code>style.sst</code>

<pre class="code">
&lt;workbook bgcolor="#FFFFFF">

  &lt;style name="header" fontColor="green" fontWeight="bold" 
         foreground="#C9C9C9" fillPattern="solid-foreground"/>
  &lt;style name="highlight" border="medium-dashed" borderColor="blue"/>
  
  &lt;sheet name="Styled Spreadsheet">
    &lt;row>
      &lt;cell style="header" colspan="3">Styles Example&lt;/cell>
    &lt;/row>
    &lt;row/>
    &lt;row>
      &lt;cell style="highlight">Value 1&lt;/cell>
      &lt;cell>Value 2&lt;/cell>
      &lt;cell style="highlight">Value 3&lt;/cell>
    &lt;/row>
  &lt;/sheet>
&lt;/workbook>
</pre>

<p>
<img src="doc-files/style.png" alt="Named Styles Example">
</p>

<h4>Formulas</h4>

<p>
SsTemplates supports all Excel formulas. 
</p>

<code>formula.sst</code>

<pre class="code">
&lt;workbook>
  &lt;style name="label" fontWeight="bold"/>
  &lt;style name="money" dataFormat="$0.00"/>

  &lt;sheet name="Formula Spreadsheet">
    &lt;row>
      &lt;cell style="label">January&lt;/cell>
      &lt;cell style="money" type="numeric">1.99&lt;/cell>
    &lt;/row>
    &lt;row>
      &lt;cell style="label">February&lt;/cell>
      &lt;cell style="money" type="numeric">2.09&lt;/cell>
    &lt;/row>
    &lt;row>
      &lt;cell style="label">March&lt;/cell>
      &lt;cell style="money" type="numeric">3&lt;/cell>
    &lt;/row>
    &lt;row>
      &lt;cell style="label">Total&lt;/cell>
      &lt;cell style="money" type="formula">sum(B1:B3)&lt;/cell>
    &lt;/row>
  &lt;/sheet>
&lt;/workbook>
</pre>

<p>
<img src="doc-files/formula.png" alt="Formula Example">
</p>

<h4>Expression Language and Control Tags</h4>

<p>
SsTemplates includes expression language support and flow control tags designed to match the functionality of JSP 2.0 JSTL. This includes tags <code>&lt;set></code>, <code>&lt;if></code>, and <code>&lt;forEach></code>.
</p>

<p>
When using SsTemplates in a servlet container, an SST file has access to the same implicit EL variables as a JSP. Request-scoped attributes are available as EL variable names and request parameters are available through the implicit 'param' variable. This is the key to the power of SsTemplates - you provide Java objects to a template to render using standard EL and flow control tags.
</p>

<code>el.sst</code>

<pre class="code">
&lt;workbook xmlns="http://carbonfive.com/schema/sstemplates">
  &lt;set var="exists" value="${!empty param.name}"/>

  &lt;sheet name="EL Spreadsheet">
    &lt;row>
      &lt;if test="${!exists}">
        &lt;cell>Default name:&lt;/cell>
        &lt;cell>John&lt;/cell>
      &lt;/if>
      &lt;if test="${exists}">
        &lt;cell>Provided names:&lt;/cell>
        &lt;forEach var="name" items="${paramValues.name}">
          &lt;cell>${name}&lt;/cell>
        &lt;/forEach>
      &lt;/if>
    &lt;/row>
  &lt;/sheet>
&lt;/workbook>
</pre>

<p>
<img src="doc-files/el-1.png" alt="EL Example">
</p>

<code>el.sst?name=Sue&name=Bob</code>

<p>
<img src="doc-files/el-2.png" alt="EL Example">
</p>


<h3><a name="features">Features</a></h3>

<p>
In addition to the basic features illustrated above, SsTemplates includes support for:

<ul>
<li>Print formatting control
<li>Including external SST files in an SST file
<li>Using an Excel .xml document as a template into which you insert SsTemplates output
<li>Charts, graphs and pivot tables using an existing Excel document
<li>Registering custom functions for use in EL
<li>Adding your own custom tags to extend SsTemplates functionality
</ul>
</p>

<p>
The best way to learn the specifics of these features (for now) is to run through the .sst files in the project test suite.
</p>

<h3><a name="usage">Usage</a></h3>

<p>
There are two primary usage models for SsTemplates - servlet and standalone.
</p>

<h4>Servlet</h4>

<p>
Using SsTemplates in a servlet container is very similar to using JSPs. You map *.sst files to the SsTemplateServlet in your web application's web.xml file. You can then access *.sst files directly through a web browser and the SsTemplateServlet will handle rendering Excel documents to the browser. If you provide request parameters to the SST file in a query string or by submitting a form, those parameters will be available to the SST file as illustrated in the EL Example above.
<p>

<p>
As with JSP files, you can forward to an SST file from a servlet. This allows you to use the standard Model 2 architecture of controller code (servlet) fetching and manipulating objects and then forwarding them to a view for rendering. Simply set objects as request attributes to make them available to the SST file as EL variables.
</p>

<pre class="code">
package com.carbonfive.sstemplates.examples;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class SimpleServlet extends HttpServlet
{
  public void service(HttpServletRequest request,  HttpServletResponse response)
      throws IOException, ServletException
  {
    request.setAttribute("stringValue", "Ralph");
    request.setAttribute("listValue", new String[] { "Sue", "Amy", "Donna" });
    
    request.getRequestDispatcher("/WEB-INF/templates/names.sst").forward(request, response);
  }
}
</pre>

<h4>Standalone</h4>

<p>
If you are using SsTemplates outside of a servlet container or simply want greater control over the lifecycle of template rendering, you can use the SsTemplates API. Your entry point is {@link com.carbonfive.sstemplates.SsTemplateProcessor}. You provide a template and a context that holds Java objects to the processor and get back an HSSFWorkbook object which you can then write to any output stream.
</p>

<pre class="code">
package com.carbonfive.sstemplates.examples;

import java.util.*;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import com.carbonfive.sstemplates.*;

public class StandAlone
{
  public static void main(String args[]) throws Exception
  {
    File template = new File("examples/templates/standalone.sst");

    Map context = new HashMap();
    context.put("stringValue", "Ralph");
    context.put("listValue", new String[] { "Sue", "Amy", "Donna" });

    SsTemplateProcessor processor = SsTemplateProcessor.getInstance();
    HSSFWorkbook workbook = processor.process(template, context);

    File xls = new File("standalone.xls");
    OutputStream out = new FileOutputStream(xls);
    try
    {
      workbook.write(out);
    }
    finally
    {
      out.close();
    }
  }
}
</pre>

<h4>Authoring Templates</h4>

<p>
Authoring SST files is really a matter of learning the tag syntax for SsTemplates. We have made efforts to keep the tag syntax consistent with JSP 2.0 JSTL tags and to borrow from HTML and CSS where possible.
</p>

<p>
To assist in authoring SST files, we maintain an XML schema document that describes the valid syntax for SST files. Many IDE's provide schema validation. You must remember to include the schema reference in the root <code>&lt;workbook></code> element to get schema validation. Additionally you will need to register the schema file, sstemplates.xsd, provided in the distribution with your IDE for the URI "http://carbonfive.com/schema/sstemplates". SsTemplates does NOT do schema validation at runtime.
</p>

<pre class="code">
&lt;workbook xmlns="http://carbonfive.com/schema/sstemplates">
  &lt;sheet name="Schema Example">
    &lt;row>
      &lt;cell>Am I valid?&lt;/cell>
    &lt;/row>
  &lt;/sheet>
&lt;/workbook>
</pre>

<h3><a name="download">Download</a></h3>

The latest release of SsTemplates is available for download from <a target="_top"  href="http://sourceforge.net/project/showfiles.php?group_id=52145">SourceForge.net</a>. For the adventurous, you can check out the <a target="_top" href="http://sourceforge.net/cvs/?group_id=52145">latest changes from CVS</a>.


<h3><a name="prerequisites">Prerequisites</a></h3>

<p>
SsTemplates uses several open source libraries from the Apache Jakarta project. These libraries are included with the distribution. You may need to reconcile these library versions with versions of the same libraries that you use in your own project.
</p>

<ul>
<li>Jakarta POI - <a target="_top" href="http://jakarta.apache.org/poi/">home page</a>
<li>Jakarta Commons Digester - <a target="_top" href="http://jakarta.apache.org/commons/digester.html">home page</a>
<li>Jakarta Commons EL - <a target="_top" href="http://jakarta.apache.org/commons/el.html">home page</a>
<li>Jakarta Commons BeanUtils - <a target="_top" href="http://jakarta.apache.org/commons/beanutils.html">home page</a>
<li>Jakarta Commons Collections - <a target="_top" href="http://jakarta.apache.org/commons/collections.html">home page</a>
<li>Jakarta Commons Lang - <a target="_top" href="http://jakarta.apache.org/commons/lang.html">home page</a>
<li>Jakarta Commons Logging - <a target="_top" href="http://jakarta.apache.org/commons/logging.html">home page</a>
</ul>

<h3><a name="install">Install</a></h3>

<ol>
<li>Copy prerequisite libraries to your application classpath:
  <ul>
  <li>poi-x.x.jar
  <li>commons-digester-x.x.jar
  <li>commons-el-x.x.jar
  <li>commons-beanutils-core-x.x.jar
  <li>commons-collections-x.x.jar
  <li>commons-lang-x.x.jar
  <li>commons-logging-x.x.jar
  </ul>
<li>Copy sstemplates.jar to your application classpath.
<li>If running as a servlet, register the SsTemplateServlet in you web.xml file:

<pre class="code">
&lt;servlet>
  &lt;servlet-name>SsTemplateServlet&lt;/servlet-name>
  &lt;servlet-class>com.carbonfive.sstemplates.servlet.SsTemplateServlet&lt;/servlet-class>
  &lt;load-on-startup>1&lt;/load-on-startup>
&lt;/servlet>

&lt;servlet-mapping>
  &lt;servlet-name>SsTemplateServlet&lt;/servlet-name>
  &lt;url-pattern>*.sst&lt;/url-pattern>
&lt;/servlet-mapping>
</pre>
</ol>

<h3><a name="issues">Known Issues</a></h3>

<p>
SsTemplates is in use in production applications by many Carbon Five clients. Known issues include:
</p>

<ul>
<li>No <code>&lt;choose></code> tag to match JSTL functionality
<li>Comprehensive tag documentation is missing
<li>sstemplates.xsd schema file does not enumerate all possible attribute values
</ul>

<h3><a name="support">Support</a></h3>

For support, please read and post to the <a target="_top"  href="http://sourceforge.net/forum/forum.php?forum_id=494918">forums on SourceForge.net</a>.

<h3><a name="developers">Developers</a></h3>

<p>
SsTemplates is really a wrapper around the HSSF libraries included in the <a target="_top" href="http://jakarta.apache.org/poi/">Jakarta POI</a> project. To understand the internals of SsTemplates, 
you'll need to get familiar with HSSF.
</p>

<p>
The architecture for SsTemplates is fairly simple:
<ol>
<li>Load and parse an XML template (uses <a target="_top" href="http://jakarta.apache.org/commons/digester/">Jakarta Digester</a>)
<li>Process and evaluate the template (uses <a target="_top" href="http://jakarta.apache.org/commons/digester/">Jakarta EL</a>)
<li>Render the results as an HSSFWorkbook (uses <a target="_top" href="http://jakarta.apache.org/poi/">Jakarta POI</a>)
</ol>

<p>
For those interesting in peeking under the hood or making changes, you can check out the <a target="_top" href="http://sourceforge.net/cvs/?group_id=52145">latest changes from CVS</a> from the module named 'sstemplates'. The project includes an Ant build script and JUnit unit tests that can be used to make changes. The unit test suite provides excellent code coverage and is the best way to understand the internals of SsTemplates.
</p>

<h3><a name="reference">Reference</a></h3>

<ul>
<li> <a href="http://jcp.org/aboutJava/communityprocess/final/jsr152/index.html">JSP 2.0 Specification</a><br/>
     Detailed documentation of EL.
<li> <a href="http://java.sun.com/developer/technicalArticles/javaserverpages/JSP20/">JSTL tutorial from Sun</a>
<li> <a href="http://otn.oracle.com/pub/articles/cioroianu_jspapi.html">JSTL tutorial from Oracle</a>
<li> <a target="_top" href="http://jakarta.apache.org/poi/">Jakarta POI</a><br/>
     Provides the core XLS file generating engine.</li>
<li> <a target="_top" href="http://jakarta.apache.org/commons/digester/">Jakarta Digester</a><br/>
     Parses SsTemplate files.</li>
<li> <a target="_top" href="http://jakarta.apache.org/commons/el/">Jakarta EL</a><br/>
     Provides expression language features for scripting in SsTemplate files.</li>
</ul>

<h3><a name="license">License</a></h3>

<p>
SsTemplates is distributed with the permissive <a target="_top" href="http://www.opensource.org/licenses/bsd-license.php">BSD License</a>.
</p>

<h3><a name="credits">Credits</a></h3>

Alex Cruikshank, <a href="http://www.carbonfive.com">Carbon Five</a>, primary author<br>
Mike Wynholds, <a href="http://www.carbonfive.com">Carbon Five</a><br>
Dan Foygel, <a href="http://www.carbonfive.com">Carbon Five</a><br>
Alon Salant, <a href="http://www.carbonfive.com">Carbon Five</a><br>


</body>
</html>